package com.useeinfo.oa.modules.repair.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.useeinfo.framework.extend.dao.CrudDao;
import com.useeinfo.framework.sugar.data.QueryParam;
import com.useeinfo.framework.sugar.data.QueryUtils;
import com.useeinfo.framework.sugar.tools.Configuration;
import com.useeinfo.framework.sugar.tools.DateTimeUtils;
import com.useeinfo.framework.sugar.tools.StringConverters;
import com.useeinfo.oa.common.utils.NumberUtil;
import com.useeinfo.oa.modules.base.biz.FacilityCatBiz;
import com.useeinfo.oa.modules.base.entity.*;
import com.useeinfo.oa.modules.repair.entity.RepairOrder;
import com.useeinfo.oa.modules.repair.entity.RepairOrderLog;
import com.useeinfo.oa.modules.repair.enums.*;
import com.useeinfo.oa.modules.security.biz.OrganizationBiz;
import com.useeinfo.oa.modules.security.entity.SystemUser;
import com.useeinfo.oa.modules.security.util.SessionKeyUtils;
import com.xiaoleilu.hutool.lang.DateTime;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.servlet.http.HttpServletRequest;
import java.util.*;

/**
 * @Auther: xudong.sun
 * @Date: 2018/12/4 11:09
 * @Description:
 */
@Repository("repairOrderDao")
public class RepairOrderDao implements CrudDao<RepairOrder> {

    private final static Logger logger = LoggerFactory.getLogger(RepairOrderDao.class);


    @Autowired
    private HttpServletRequest httpServletRequest;

    @Autowired
    private RepairOrderLogDao repairOrderLogDao;

    @Autowired
    private FacilityCatBiz facilityCatBiz;
    @Autowired
    private OrganizationBiz organizationBiz;


    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Map<String, Object> getSearchCondition(Map<String, String> queryHash) {
        Map<String, Object> conditionHash = new LinkedHashMap<>();
        conditionHash.put("s.deleteStatus = ?{paramIndex} ", false);
        if (queryHash == null || queryHash.size() == 0) {
            return conditionHash;
        }

        String searchCode = queryHash.get("searchCode");
        if (StringUtils.isNotBlank(searchCode)) {
            conditionHash.put("s.reportCode like ?{paramIndex} ", "%" + searchCode + "%");
        }
        String customer = queryHash.get("customer");
        if (StringUtils.isNotBlank(customer)) {
            conditionHash.put("s.customer like ?{paramIndex} ", "%" + customer + "%");
        }
        String callNumber = queryHash.get("callNumber");
        if (StringUtils.isNotBlank(callNumber)) {
            conditionHash.put("s.callNumber like ?{paramIndex} ", "%" + callNumber + "%");
        }
        String reportContent = queryHash.get("reportContent");
        if (StringUtils.isNotBlank(reportContent)) {
            conditionHash.put("s.reportContent like ?{paramIndex} ", "%" + reportContent + "%");
        }
        String id = queryHash.get("id");
        if (StringUtils.isNotBlank(id)) {
            conditionHash.put("s.id = ?{paramIndex}", StringConverters.null2Long(id));
        }
        String repairOrderTypeEnum = queryHash.get("repairOrderTypeEnum");
        if (StringUtils.isNotBlank(repairOrderTypeEnum)) {
            conditionHash.put("s.repairOrderTypeEnum = ?{paramIndex}", RepairOrderTypeEnum.valueOf(repairOrderTypeEnum));
        }
        String unComplete = queryHash.get("unComplete");
        if (StringUtils.isNotBlank(unComplete)) {
            conditionHash.put("s.repairOrderStatusEnum != ?{paramIndex}", RepairOrderStatusEnum.valueOf(unComplete));
        }
        String completed = queryHash.get("completed");
        if (StringUtils.isNotBlank(completed)) {
            conditionHash.put("s.repairOrderStatusEnum = ?{paramIndex}", RepairOrderStatusEnum.valueOf(completed));
        }

        String AUTOMATIC_REPAIR = queryHash.get("AUTOMATIC_REPAIR");
        if (StringUtils.isNotBlank(AUTOMATIC_REPAIR)) {
            conditionHash.put("s.repairOrderTypeEnum != ?{paramIndex}", RepairOrderTypeEnum.valueOf(AUTOMATIC_REPAIR));
        }

        String auditUserId = queryHash.get("auditUserId");
        String auditPositionId = queryHash.get("auditPositionId");
        if (StringUtils.isNotBlank(auditUserId) && StringUtils.isBlank(auditPositionId)) {
            conditionHash.put("s.auditUser.id = ?{paramIndex}", StringConverters.null2Long(auditUserId));
        } else if (StringUtils.isBlank(auditUserId) && StringUtils.isNotBlank(auditPositionId)) {
            conditionHash.put("s.auditPosition.id = ?{paramIndex}", StringConverters.null2Long(auditPositionId));
        } else if (StringUtils.isNotBlank(auditUserId) && StringUtils.isNotBlank(auditPositionId)) {
            conditionHash.put("(s.auditUser.id = ?{paramIndex} or s.auditPosition.id = " + auditPositionId + " )", StringConverters.null2Long(auditUserId));
        }


        String auditOrganizationId = queryHash.get("auditOrganizationId");
        if (StringUtils.isNotBlank(auditOrganizationId)) {
            conditionHash.put("s.auditOrganization.id = ?{paramIndex}", StringConverters.null2Long(auditOrganizationId));
        }

        String enterpriseId = queryHash.get("enterpriseId");
        if (StringUtils.isNotBlank(enterpriseId)) {
            conditionHash.put("s.enterprise.id = ?{paramIndex}", StringConverters.null2Long(enterpriseId));
        }

        String enterpriseIds = queryHash.get("enterpriseIds");
        if (StringUtils.isNotBlank(enterpriseIds)) {
            conditionHash.put("s.enterprise.id in(" + enterpriseIds + ") and 1= ?{paramIndex}", 1);
        }

        String beforeCreateDate = queryHash.get("beforeCreateDate");
        if (StringUtils.isNotBlank(beforeCreateDate)) {
            conditionHash.put("s.createDate > ?{paramIndex} ", DateTimeUtils.parse("yyyy-MM-dd HH:mm:ss", beforeCreateDate));
        }

        String createDate1 = queryHash.get("createDate1");
        if (StringUtils.isNotBlank(createDate1)) {
            conditionHash.put("s.createDate >= ?{paramIndex} ", StringConverters.ToDateOnly(createDate1));
        }
        String createDate2 = queryHash.get("createDate2");
        if (StringUtils.isNotBlank(createDate2)) {
            conditionHash.put("s.createDate <= ?{paramIndex} ", DateTimeUtils.getEndOfDay(StringConverters.ToDateOnly(createDate2)));
        }
        String beforeRepairFinishTime = queryHash.get("beforeRepairFinishTime");
        if (StringUtils.isNotBlank(beforeRepairFinishTime)) {
            conditionHash.put("s.repairFinishTime > ?{paramIndex} ", DateTimeUtils.parse("yyyy-MM-dd HH:mm:ss", beforeRepairFinishTime));
        }

        String reportCode = queryHash.get("reportCode");
        if (StringUtils.isNotBlank(reportCode)) {
            conditionHash.put("s.reportCode like ?{paramIndex}", "%" + reportCode + "%");

        }
        String repairType = queryHash.get("repairType");
        if (StringUtils.isNotBlank(repairType)) {
            conditionHash.put("s.repairType.propertyName like ?{paramIndex}", "%"+repairType+"%" );

        }

        String reportAreaIds = queryHash.get("reportAreaIds");
        if (StringUtils.isNotBlank(reportAreaIds)) {
            conditionHash.put("s.reportArea.id in (?{paramIndex})", StringConverters.null2Long(reportAreaIds));
        }
        String reportArea = queryHash.get("reportArea");
        if (StringUtils.isNotBlank(reportArea)) {
            conditionHash.put("s.reportArea.areaName like ?{paramIndex}", "%" + reportArea + "%");
        }

        String facilityId = queryHash.get("facilityId");
        if (StringUtils.isNotBlank(facilityId)) {
            conditionHash.put("s.facility.id = ?{paramIndex}", StringConverters.null2Long(facilityId));
        }

        String repairOrderStatus = queryHash.get("repairOrderStatus");
        if (StringUtils.isNotBlank(repairOrderStatus)) {
            String[] statusArray = repairOrderStatus.split(",");
            for (int i = 0; i < statusArray.length; i++) {
                if (i == 0) {
                    String k = statusArray.length == 1 ? "repairOrderStatusEnum = ?{paramIndex} " : "(repairOrderStatusEnum = ?{paramIndex} ";
                    conditionHash.put(k, RepairOrderStatusEnum.valueOf(statusArray[i].trim()));
                } else if (i == statusArray.length - 1) {
                    conditionHash.put("or repairOrderStatusEnum = ?{paramIndex}) ", RepairOrderStatusEnum.valueOf(statusArray[i].trim()));
                } else {
                    String k = String.format("or repairOrderStatusEnum = ?{paramIndex} %" + i + "s", "");
                    conditionHash.put(k, RepairOrderStatusEnum.valueOf(statusArray[i].trim()));
                }
            }
        }

        String repairSource = queryHash.get("repairSource");
        if (StringUtils.isNotBlank(repairSource)) {
            String[] sourceArray = repairSource.split(",");
            for (int i = 0; i < sourceArray.length; i++) {
                if (i == 0) {
                    String k = sourceArray.length == 1 ? "repairSourceEnum = ?{paramIndex} " : "(repairSourceEnum = ?{paramIndex} ";
                    conditionHash.put(k, RepairSourceEnum.valueOf(sourceArray[i].trim()));
                } else if (i == sourceArray.length - 1) {
                    conditionHash.put("or repairSourceEnum = ?{paramIndex}) ", RepairSourceEnum.valueOf(sourceArray[i].trim()));
                } else {
                    String k = String.format("or repairSourceEnum = ?{paramIndex} %" + i + "s", "");
                    conditionHash.put(k, RepairSourceEnum.valueOf(sourceArray[i].trim()));
                }
            }
        }

        String receiveUser = queryHash.get("receiveUser");
        if (StringUtils.isNotBlank(receiveUser)) {
            conditionHash.put("s.receiveUser.id = ?{paramIndex}", StringConverters.null2Long(receiveUser));
        }

        String reportOrReceiveUser = queryHash.get("reportOrReceiveUser");
        if (StringUtils.isNotBlank(reportOrReceiveUser)) {
            conditionHash.put("(s.receiveUser.id = ?{paramIndex} or s.reportUser.id = ?{paramIndex})", StringConverters.null2Long(reportOrReceiveUser));
        }
        String reportUserID = queryHash.get("reportUserID");
        if (StringUtils.isNotBlank(reportUserID)) {
            conditionHash.put(" s.reportUser.id = ?{paramIndex}", StringConverters.null2Long(reportUserID));
        }
        String reportUser = queryHash.get("reportUser");
        if (StringUtils.isNotBlank(reportUser)) {
            conditionHash.put(" s.reportUser.userName like ?{paramIndex}", "%" + reportUser + "%");
        }
        String grade = queryHash.get("grade");
        if (StringUtils.isNotBlank(grade)) {
            conditionHash.put(" s.reportLevel.grade in (" + grade + ") and 1= ?{paramIndex}", 1);
        }
        //今日完成
        String completeDate1 = queryHash.get("completeDate1");
        if (StringUtils.isNotBlank(completeDate1)) {
            conditionHash.put(" s.repairFinishTime is not null and s.repairFinishTime  >= ?{paramIndex}",  StringConverters.ToDateTime(completeDate1));
        }
        String completeDate2 = queryHash.get("completeDate2");
        if (StringUtils.isNotBlank(completeDate2)) {
            conditionHash.put(" s.repairFinishTime is not null and s.repairFinishTime <= ?{paramIndex}",  StringConverters.ToDateTime(completeDate2));
        }
        return conditionHash;
    }

    @Override
    public Long totalRecord(Map<String, String> queryHash) {
        Map<String, Object> conditions = getSearchCondition(queryHash);
        TypedQuery<Long> typedQuery = QueryUtils.getTypedQueryByCondition("select count(s) from RepairOrder s ", conditions, entityManager, Long.class);
        return typedQuery.getSingleResult();
    }

    @Override
    public List<RepairOrder> findList(QueryParam queryParam) {
        String sqlInfo = queryParam.joinJPQL("select s from RepairOrder s ", "order by s.id desc ");
        Map<String, Object> conditions = getSearchCondition(queryParam.getSqlMap());
        TypedQuery<RepairOrder> typedQuery = QueryUtils.getTypedQueryByCondition(sqlInfo, conditions, entityManager, RepairOrder.class);
        return queryParam.findPageList(typedQuery);
    }

    @Override
    public RepairOrder findModel(Long id) {
        return entityManager.find(RepairOrder.class, id);
    }

    @Override
    public Integer add(RepairOrder model) {
        model.setCreateDate(new Date());
        entityManager.persist(model);
        logger.info("RepairOrderDaoImpl添加RepairOrder成功！");
        return 1;
    }

    public Integer warningAdd(RepairOrder model) {
        model.setCreateDate(new Date());
        entityManager.persist(model);
        RepairOrderLog repairOrderLog = new RepairOrderLog();
        repairOrderLog.setRepairOrder(model);
        repairOrderLog.setRepairOrderLogOperationEnum(RepairOrderLogOperationEnum.CREATE_ORDER);
        repairOrderLog.setCreateDate(new Date());
        repairOrderLogDao.add(repairOrderLog);
        logger.info("RepairOrderDaoImpl添加RepairOrder成功！");
        return 1;
    }

    @Override
    public Integer update(RepairOrder model) {
        model.setUpdateDate(new Date());
        entityManager.merge(model);
        return 1;
    }

    @Override
    public Integer delete(Long id) {
        RepairOrder repairOrder = entityManager.find(RepairOrder.class, id);
        entityManager.remove(repairOrder);
        return 1;
    }

    @Override
    public Integer deleteLogic(RepairOrder model) {
        model.setDeleteStatus(true);
        return 1;
    }

    /**
     * 获取维修单编号
     *
     * @param enterpriseId
     * @return
     */
    public String getCode(String enterpriseId) {
        String code = enterpriseId + "-" + DateTimeUtils.formatDateToStringWithOnlyDate(new Date(), "yyyyMMdd") + "-" + (int) ((Math.random() * 9 + 1) * 100000);
        QueryParam queryParam = new QueryParam();
        queryParam.getSqlMap().put("reportCode", code);
        List<RepairOrder> repairOrders = findList(queryParam);
        if (CollectionUtils.isNotEmpty(repairOrders)) {
            return getCode(enterpriseId);
        } else {
            return code;
        }
    }

    /**
     * 推送维修单
     * @param repairOrder 单号
     * @param enterprise 企业
     * @param ids 负责人
     * @return
     */
    public List<SystemUser> sendRepairUsers(RepairOrder repairOrder, Enterprise enterprise, List<Long> ids) {
        String jpql = "SELECT s FROM SystemUser s WHERE 1 = 1 " +
                " AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) " +
                "AND s.operationRole = 'common' " +
                "AND s.deleteStatus = false ";
        if (repairOrder.getAssign()) {
            if (CollectionUtils.isNotEmpty(ids) && AssignTypeEnum.USER.getCode().equals(repairOrder.getAssignTypeEnum().getCode())) {
                jpql += "AND s.id in (" + StringUtils.strip(ids.toString(), "[]") + ") ";
            } else if (AssignTypeEnum.POSITION.getCode().equals(repairOrder.getAssignTypeEnum().getCode())) {
                if (CollectionUtils.isNotEmpty(ids)) {
                    jpql += "AND exists (SELECT a from s.positionSet a Where a.id in (" + StringUtils.strip(ids.toString(), "[]") + ")) ";
                }
            }
        }
        if (!repairOrder.getAssign() && repairOrder.getReportArea() != null) {
            jpql += "AND " + repairOrder.getReportArea().getId() + " in elements(s.areaSet) ";
        }
        logger.info("执行的sql语句为：{}", jpql);
        TypedQuery<SystemUser> resultQuery = this.entityManager.createQuery(jpql, SystemUser.class);
        return resultQuery.getResultList();
    }

    public List<SystemUser> confirmSendUser(Enterprise enterprise, Position position, Organization organization, SystemUser systemUser) {
        String jpql = "SELECT s FROM SystemUser s  WHERE 1 = 1 " +
                "AND s.deleteStatus = false " +
                "AND s.operationRole = 'common' " +
                "AND " + StringConverters.null2Long(enterprise.getId()) + " in elements(s.enterpriseSet) ";
        if (organization != null) {
            jpql += "AND exists (SELECT ss FROM s.systemUserOrganizationSet ss WHERE ss.organization.id = " + StringConverters.null2Long(organization.getId()) + ") ";
        }
        if (position != null) {
            jpql += "AND " + position.getId() + " in elements(s.positionSet) ";
        }
        if (systemUser != null) {
            jpql += "AND s.id = " + systemUser.getId() + " ";
        }
        TypedQuery<SystemUser> resultQuery = this.entityManager.createQuery(jpql, SystemUser.class);
        return resultQuery.getResultList();
    }


    /**
     * 转单列表
     * @param searchParam
     * @param pageIndex
     * @param pageSize
     * @param systemUser 登陆用户
     * @param enterprise 企业
     * @return
     */
    public JSONObject transferList(String searchParam, String pageIndex, String pageSize, SystemUser systemUser, Enterprise enterprise,Organization organization) {
        JSONObject result = new JSONObject();
        int index = 0;
        int size = 0;
        if (StringUtils.isNotBlank(pageIndex)) {
            index = StringConverters.null2Int(pageIndex);
        }

        if (StringUtils.isNotBlank(pageSize)) {
            size = StringConverters.null2Int(pageSize);
        }

        String jpql = "SELECT s FROM RepairOrder s  WHERE 1 = 1 " +
                " AND s.enterprise.id = " + StringConverters.null2Long(enterprise.getId()) +
                " AND s.deleteStatus = false " +
                " AND s.repairOrderTypeEnum in ('COMMON_ORDER','FACILITY_ORDER','_400_ORDER') " +
                " AND s.repairSourceEnum = 'CUSTOMER_REPORT' " +
                " AND s.repairOrderStatusEnum in ('WAIT_PRESENCE','WAIT_CONFIRM_FAULT','UNFINISHED','REJECTED') ";


        if(organization != null) {
            //接单人不是自己 部门下及下级部门
            jpql += " AND (s.receiveUser.id = " + StringConverters.null2Long(systemUser.getId()) + "  " +
                    "OR (s.receiveUser.id != " + StringConverters.null2Long(systemUser.getId()) + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%')))";
        } else {
            jpql += " AND s.receiveUser.id = " + StringConverters.null2Long(systemUser.getId()) + " ";
        }

        if (StringUtils.isNotBlank(searchParam)) {
            jpql += "AND (s.reportCode like '%" + searchParam + "%' OR s.reportAddress like '%" + searchParam + "%' OR s.reportContent like '%" + searchParam + "%') ";
        }
        TypedQuery<RepairOrder> resultQuery = this.entityManager.createQuery(jpql, RepairOrder.class);
        int records = resultQuery.getResultList().size();
        if (index > 0 && size > 0) {
            int minLimit = size * (index - 1);
            int maxLimit = size;
            resultQuery.setFirstResult(minLimit).setMaxResults(maxLimit);
        }
        List<RepairOrder> resultList = resultQuery.getResultList();
        JSONArray rows = new JSONArray();
        for (RepairOrder repairOrder : resultList) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("createDate", DateTimeUtils.format(repairOrder.getCreateDate(), DateTimeUtils.format(repairOrder.getCreateDate(), "MM-dd hh:mm")));
            jsonObject.put("reportContent", StringConverters.null2String(repairOrder.getReportContent()));
            jsonObject.put("reportAddress", StringConverters.null2String(repairOrder.getReportAddress()));
            jsonObject.put("orderStatus", repairOrder.getRepairOrderStatusEnum().getName());
            jsonObject.put("reportCode", repairOrder.getReportCode());
            jsonObject.put("reportUser", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getUserName());
            jsonObject.put("reportUserId", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getId());
            jsonObject.put("id", repairOrder.getId());
            rows.add(jsonObject);
        }

        result.put("rows", rows);
        result.put("records", records);
        return result;
    }

    /**
     * 自动报修单转单列表
     *
     * @param searchParam
     * @param pageIndex
     * @param pageSize
     * @param systemUser
     * @param enterprise
     * @return
     */
    public JSONObject autoTransferList(String searchParam, String pageIndex, String pageSize, SystemUser systemUser, Enterprise enterprise) {
        JSONObject result = new JSONObject();
        int index = 0;
        int size = 0;
        if (StringUtils.isNotBlank(pageIndex)) {
            index = StringConverters.null2Int(pageIndex);
        }

        if (StringUtils.isNotBlank(pageSize)) {
            size = StringConverters.null2Int(pageSize);
        }

        String jpql = "SELECT s FROM RepairOrder s  WHERE 1 = 1 " +
                " AND s.enterprise.id = " + StringConverters.null2Long(enterprise.getId()) +
                " AND s.deleteStatus = false " +
                " AND s.receiveUser.id = " + StringConverters.null2Long(systemUser.getId()) + " " +
                " AND s.repairOrderTypeEnum in ('AUTOMATIC_REPAIR') " +
                " AND s.repairSourceEnum = 'WUHAN_GENERAL' " +
                " AND s.repairOrderStatusEnum in ('WAIT_PRESENCE','WAIT_CONFIRM_FAULT','UNFINISHED','REJECTED') ";
        if (StringUtils.isNotBlank(searchParam)) {
            jpql += "AND (s.reportCode like '%" + searchParam + "%' OR s.reportAddress like '%" + searchParam + "%' OR s.reportContent like '%" + searchParam + "%') ";
        }
        TypedQuery<RepairOrder> resultQuery = this.entityManager.createQuery(jpql, RepairOrder.class);
        int records = resultQuery.getResultList().size();
        if (index > 0 && size > 0) {
            int minLimit = size * (index - 1);
            int maxLimit = size;
            resultQuery.setFirstResult(minLimit).setMaxResults(maxLimit);
        }
        List<RepairOrder> resultList = resultQuery.getResultList();
        JSONArray rows = new JSONArray();
        for (RepairOrder repairOrder : resultList) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("createDate", DateTimeUtils.format(repairOrder.getCreateDate(), DateTimeUtils.format(repairOrder.getCreateDate(), "MM-dd hh:mm")));
            jsonObject.put("reportContent", StringConverters.null2String(repairOrder.getReportContent()));
            jsonObject.put("reportAddress", StringConverters.null2String(repairOrder.getReportAddress()));
            jsonObject.put("orderStatus", repairOrder.getRepairOrderStatusEnum().getName());
            jsonObject.put("reportCode", repairOrder.getReportCode());
            jsonObject.put("reportUser", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getUserName());
            jsonObject.put("reportUserId", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getId());
            jsonObject.put("id", repairOrder.getId());
            rows.add(jsonObject);
        }

        result.put("rows", rows);
        result.put("records", records);
        return result;
    }

    /**
     * 获取工单列表
     */
    public JSONObject getRepairOrders(String pageIndex, String pageSize, String enterpriseId, String userId, Set<Long> areaIds, String positionId, String status, boolean leader, Organization organization, String searchParam, String date,String startDate, String endDate,String catId,String areaId,String levelId,String typeId,String repairPosition,String receiveUser) {
        JSONObject result = new JSONObject();
        int index = 0;
        int size = 0;
        if (StringUtils.isNotBlank(pageIndex)) {
            index = StringConverters.null2Int(pageIndex);
        }

        if (StringUtils.isNotBlank(pageSize)) {
            size = StringConverters.null2Int(pageSize);
        }

        String jpql = "SELECT s FROM RepairOrder s  WHERE 1 = 1 " +
                " AND s.enterprise.id = " + StringConverters.null2Long(enterpriseId) +
                " AND s.deleteStatus = false " +
                " AND s.repairOrderTypeEnum in ('COMMON_ORDER','FACILITY_ORDER','_400_ORDER') " +
                " AND s.repairSourceEnum in ('CUSTOMER_REPORT') ";
        //待接单
        if ("1".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum = 'WAIT_TAKING' ";
            //判断非指派单（有区域的维修单判断人员是否在区域内 + 无区域的维修单）
            jpql += " AND (( s.assign = false AND ( s.reportArea IS NULL ";
            if (CollectionUtils.isNotEmpty(areaIds)) {
                jpql += " OR  s.reportArea.id IN (" + StringUtils.strip(areaIds.toString(), "[]") + ")";
            }
            //判断指派单（指派人的工单 + 无区域指派岗位的工单 + 有区域有岗位的工单）
            jpql += ")) OR ( s.assign = true AND (" + StringConverters.null2Long(userId) + " in elements(s.assignUsers) ";
            if (StringUtils.isNotBlank(positionId)) {
                jpql += " OR (s.reportArea is null AND " + StringConverters.null2Long(positionId) + " in elements(s.assignPositions))";
                if (CollectionUtils.isNotEmpty(areaIds)) {
                    jpql += " OR (s.reportArea is not null AND s.reportArea.id IN (" + StringUtils.strip(areaIds.toString(), "[]") + ") AND " + StringConverters.null2Long(positionId) + " in elements(s.assignPositions)) ";
                }
            }
            jpql += " ))) ";
            //未完成（人员为接单人）
        } else if ("2".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum IN ('WAIT_PRESENCE','WAIT_CONFIRM_FAULT','UNFINISHED','REJECTED') ";
            if (StringUtils.isNotBlank(date)) {
                jpql += " and s.receiveDate <='" + date + "'";
            }
            //非领导查询语句
            // 接单人为自己
            if (!leader) {
                jpql += " AND s.receiveUser.id = " + userId + " ";
            }
            //领导查询语句
            if (leader) {
                //接单人不是自己 部门下及下级部门
                jpql += " AND (s.receiveUser.id = " + userId + "  " +
                        "OR (s.receiveUser.id != " + userId + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%')))";
            }
            //审核中
        } else if ("3".equals(status)) {
            if (organization == null) {
                return result;
            }

            jpql += " AND s.repairOrderStatusEnum = 'IN_AUDITING' ";
            /**
             * 人员在审核节点中 部门 - 人员 部门 - 岗位
             */
            jpql += " AND (s.receiveUser.id = " + userId + " OR (s.auditOrganization.id = " + organization.getId() + " AND ( s.auditUser.id = " + userId +" OR  s.auditPosition.id = " + positionId + " ) ";
            if(leader){
                jpql=jpql+ "OR (s.receiveUser.id != " + userId + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))";
            }

            jpql += " )) ";

        } else if ("4".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum IN ('CANCELED','COMPLETED') ";
            //非领导查询语句
            /**
             * 接单人为自己
             */
            if (!leader) {
                jpql += " AND s.receiveUser.id = " + userId + " ";
            }

            if (leader) {
                //接单人不是自己 部门下及下级部门
                jpql += " AND (s.receiveUser.id = " + userId + "  " +
                        "OR (s.receiveUser.id != " + userId + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";
            }
        }

        //查询条件

        if (StringUtils.isNotBlank(searchParam)) {
            jpql += " AND (s.reportCode like '%" + searchParam + "%' OR s.reportAddress like '%" + searchParam + "%' OR s.reportContent like '%" + searchParam + "%') ";
        }
        if (StringUtils.isNotBlank(startDate)) {//接单时间查询
            Date start=StringConverters.ToDateTime(startDate);
            jpql += " AND s.createDate >= "+start;
        }
        if (StringUtils.isNotBlank(endDate)) {//接单时间查询
            Date end=StringConverters.ToDateTime(endDate);
            jpql += " AND s.createDate <= "+end;
        }
        if (StringUtils.isNotBlank(repairPosition)) {//报修地址
            jpql += " AND s.reportAddress like '%"+repairPosition+"%'";
        }
        if (StringUtils.isNotBlank(receiveUser)) {//接单人
            jpql += " AND s.receiveUser is not null and  s.receiveUser.userName  like '%"+typeId+"%'";
        }
        if (StringUtils.isNotBlank(areaId)) {//区域
            jpql += " AND s.facility is not null and s.facility.area is not null and s.facility.area.id ="+areaId;
        }
        if (StringUtils.isNotBlank(typeId)) {//维修单类型
            jpql += " AND s.repairType is not null and s.repairType.id ="+typeId;
        }
        if (StringUtils.isNotBlank(levelId)) {//维修等级
            jpql += " AND s.reportLevel is not null and s.reportLevel.id ="+levelId;
        }

        if ("3".equals(status)) {
            jpql += " ORDER BY s.receiveDate desc";

        } else if ("2".equals(status)) {
            jpql += " ORDER BY s.receiveDate DESC ";
        } else {
            jpql += " ORDER BY s.createDate DESC ";
        }
        TypedQuery<RepairOrder> resultQuery = this.entityManager.createQuery(jpql, RepairOrder.class);
        int records = resultQuery.getResultList().size();
        if (index > 0 && size > 0) {
            int minLimit = size * (index - 1);
            int maxLimit = size;
            resultQuery.setFirstResult(minLimit).setMaxResults(maxLimit);
        }
        List<RepairOrder> resultList = resultQuery.getResultList();
        JSONArray rows = new JSONArray();
        for (RepairOrder repairOrder : resultList) {
            //标签设置
            Date now = new Date();
            JSONArray tags = new JSONArray();
            JSONObject tagObject = new JSONObject();
            if (now.getTime() > repairOrder.getRequireFinishTime().getTime()) {
                tags.add(tagObject("逾期", "full-orange"));
            }
//            else if (repairOrder.getRequirePresenceTime()!=null&&repairOrder.getPresenceTime()==null) {
//                if (now.getTime() > repairOrder.getPresenceTime().getTime()) {
//                    tags.add(tagObject("逾期", "full-orange"));
//                }
//            }
            if (repairOrder.getReportLevel() != null) {
                tags.add(tagObject(repairOrder.getReportLevel().getGrade(), "orange"));
            }


            //审核标签
//            if ("3".equals(status)) {
//                if (repairOrder.getAuditLevel() == 0) {
//                    tags.add(tagObject("初审","blue"));
//                } else if (repairOrder.getAuditLevel() + 1 == JSONArray.parseArray(repairOrder.getFlow()).size()) {
//                    tags.add(tagObject("终审","blue"));
//                } else {
//                    tags.add(tagObject("复审","blue"));
//                }
//            }

            if (repairOrder.getAssign()) {
                tags.add(tagObject("被指派", "blue"));
            }

            if ("IN_AUDITING".equals(repairOrder.getRepairOrderStatusEnum().getCode())) {
                tags.add(tagObject(NumberUtil.convert(repairOrder.getAuditLevel() + 1) + "审", "blue"));
            }


            if (StringUtils.isNotBlank(repairOrder.getRepairOrderTypeEnum().getCode())) {
                if (RepairOrderTypeEnum.COMMON_ORDER.getCode().equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                    tags.add(tagObject("普通", "blue"));
                } else if (RepairOrderTypeEnum.FACILITY_ORDER.getCode().equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                    tags.add(tagObject("设备", "blue"));
                } else if (RepairOrderTypeEnum._400_ORDER.getCode().equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                    tags.add(tagObject("400", "blue"));
                }
            }

            if (repairOrder.getReportMajor() != null) {
                tags.add(tagObject(repairOrder.getMajorName() == null ? "" : repairOrder.getMajorName(), "blue"));
            }


            //设备标签
            if (repairOrder.getFacility() != null && repairOrder.getFacility().getFacilityCat() != null) {
                FacilityCat rootCat = facilityCatBiz.findRootCat(repairOrder.getFacility().getFacilityCat().getId());
                tags.add(tagObject(repairOrder.getFacility().getFacilityCat().getCatName(), "blue"));
                tags.add(tagObject(rootCat.getCatName(), "blue"));
            }


            JSONObject jsonObject = new JSONObject();
            jsonObject.put("createDate", DateTimeUtils.format(repairOrder.getCreateDate(), DateTimeUtils.format(repairOrder.getCreateDate(), "yy-MM-dd HH:mm")));
            jsonObject.put("reportContent", StringConverters.null2String(repairOrder.getReportContent()));
            jsonObject.put("reportAddress", StringConverters.null2String(repairOrder.getReportAddress()));
            jsonObject.put("orderStatus", repairOrder.getRepairOrderStatusEnum().getName());
            jsonObject.put("reportCode", repairOrder.getReportCode());
            jsonObject.put("reportUser", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getUserName());
            jsonObject.put("receiveUserName", repairOrder.getReceiveUser() == null ? "" : repairOrder.getReceiveUser().getUserName());
            jsonObject.put("id", repairOrder.getId());
            jsonObject.put("tags", tags);
            //如果是确认签字中，跳转到签字版页面。400维修，审批中，已经确认维修，未签字
            String signEnterpriseId=Configuration.getConfigurationByName("signEnterpriseId");//只有武汉通用才有签字
            if (signEnterpriseId.equals(enterpriseId)&&repairOrder.getRepairOrderTypeEnum().getCode().equals("_400_ORDER") && repairOrder.getRepairOrderStatusEnum().getCode().equals("IN_AUDITING") &&
                    repairOrder.isCheckRepair() && repairOrder.getSignDate() == null) {
                result.put("signConfirm", true);

            } else {
                result.put("signConfirm", false);
            }
            rows.add(jsonObject);
        }
        result.put("rows", rows);
        result.put("records", records);
        return result;
    }

    /**
     * 获取报修工单列表
     */
    public JSONObject getAutomaticRepair(String pageIndex, String pageSize, String enterpriseId, String userId, Set<Long> areaIds, String positionId, String status, boolean leader, Organization organization, String searchParam, String date) {
        JSONObject result = new JSONObject();
        int index = 0;
        int size = 0;
        if (StringUtils.isNotBlank(pageIndex)) {
            index = StringConverters.null2Int(pageIndex);
        }

        if (StringUtils.isNotBlank(pageSize)) {
            size = StringConverters.null2Int(pageSize);
        }

        String jpql = "SELECT s FROM RepairOrder s  WHERE 1 = 1 " +
                " AND s.enterprise.id = " + StringConverters.null2Long(enterpriseId) +
                " AND s.deleteStatus = false " +
                " AND s.repairSourceEnum = 'WUHAN_GENERAL' ";
        //待接单
        if ("1".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum = 'WAIT_TAKING' ";
            //判断非指派单（有区域的维修单判断人员是否在区域内 + 无区域的维修单）
            jpql += " AND (( s.assign = false AND ( s.reportArea IS NULL ";
            if (CollectionUtils.isNotEmpty(areaIds)) {
                jpql += " OR  s.reportArea.id IN (" + StringUtils.strip(areaIds.toString(), "[]") + ")";
            }
            //判断指派单（指派人的工单 + 无区域指派岗位的工单 + 有区域有岗位的工单）
            jpql += ")) OR ( s.assign = true AND (" + StringConverters.null2Long(userId) + " in elements(s.assignUsers) ";
            if (StringUtils.isNotBlank(positionId)) {
                jpql += " OR (s.reportArea is null AND " + StringConverters.null2Long(positionId) + " in elements(s.assignPositions))";
                if (CollectionUtils.isNotEmpty(areaIds)) {
                    jpql += " OR (s.reportArea is not null AND s.reportArea.id IN (" + StringUtils.strip(areaIds.toString(), "[]") + ") AND " + StringConverters.null2Long(positionId) + " in elements(s.assignPositions)) ";
                }
            }
            jpql += " ))) ";
            //未完成（人员为接单人）
        } else if ("2".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum IN ('WAIT_PRESENCE','WAIT_CONFIRM_FAULT','UNFINISHED','REJECTED') ";
            if (StringUtils.isNotBlank(date)) {
                jpql += " and s.receiveDate <='" + date + "'";
            }
            //非领导查询语句
            // 接单人为自己
            if (!leader) {
                jpql += " AND s.receiveUser.id = " + userId + " ";
            }
            //领导查询语句
            if (leader) {
                //接单人不是自己 部门下及下级部门
                jpql += " AND (s.receiveUser.id = " + userId + "  " +
                        "OR (s.receiveUser.id != " + userId + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%')))";
            }
            //审核中
        } else if ("3".equals(status)) {
            if (organization == null) {
                return result;
            }
            jpql += " AND s.repairOrderStatusEnum = 'IN_AUDITING' ";

            /**
             * 人员在审核节点中 部门 - 人员 部门 - 岗位
             */
            jpql += " AND (s.receiveUser.id = " + userId + " OR (s.auditOrganization.id = " + organization.getId() + " AND ( s.auditUser.id = " + userId + "  ";
            if (StringUtils.isNotBlank(positionId)) {
                jpql += " OR  s.auditPosition.id = " + positionId + " ) ";
            }
            jpql += " ))) ";

        } else if ("4".equals(status)) {
            jpql += " AND s.repairOrderStatusEnum IN ('CANCELED','COMPLETED') ";
            //非领导查询语句
            /**
             * 接单人为自己
             */
            if (!leader) {
                jpql += " AND s.receiveUser.id = " + userId + " ";
            }

            if (leader) {
                //接单人不是自己 部门下及下级部门
                jpql += " AND (s.receiveUser.id = " + userId + "  " +
                        "OR (s.receiveUser.id != " + userId + " AND  exists (SELECT rs FROM s.receiveUser.systemUserOrganizationSet rs WHERE rs.organization.parentIds LIKE '" + organization.getParentIds() + "%'))) ";
            }
        }

        if (StringUtils.isNotBlank(searchParam)) {
            jpql += " AND (s.reportCode like '%" + searchParam + "%' OR s.reportAddress like '%" + searchParam + "%' OR s.reportContent like '%" + searchParam + "%') ";
        }

        jpql += " ORDER BY s.createDate DESC ";
        TypedQuery<RepairOrder> resultQuery = this.entityManager.createQuery(jpql, RepairOrder.class);
        int records = resultQuery.getResultList().size();
        if (index > 0 && size > 0) {
            int minLimit = size * (index - 1);
            int maxLimit = size;
            resultQuery.setFirstResult(minLimit).setMaxResults(maxLimit);
        }
        List<RepairOrder> resultList = resultQuery.getResultList();
        JSONArray rows = new JSONArray();
        for (RepairOrder repairOrder : resultList) {
            //标签设置
            Date now = new Date();
            JSONArray tags = new JSONArray();
            if (repairOrder.getReportLevel() != null) {
                tags.add(tagObject(repairOrder.getReportLevel().getGrade(), "orange"));
            }

            if (repairOrder.getAssign()) {
                tags.add(tagObject("被指派", "blue"));
            }

            if ("IN_AUDITING".equals(repairOrder.getRepairOrderStatusEnum().getCode())) {
                tags.add(tagObject(NumberUtil.convert(repairOrder.getAuditLevel() + 1) + "审", "blue"));
            }


            if (StringUtils.isNotBlank(repairOrder.getRepairOrderTypeEnum().getCode())) {
                if ("COMMON_ORDER".equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                    tags.add(tagObject("普通", "blue"));
                } else if ("FACILITY_ORDER".equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                    tags.add(tagObject("设备", "blue"));
                }
            }

            if (repairOrder.getReportMajor() != null) {
                tags.add(tagObject(repairOrder.getReportMajor().getPropertyName(), "blue"));
            }


            //设备标签
            if (repairOrder.getFacility() != null && repairOrder.getFacility().getFacilityCat() != null) {
                FacilityCat rootCat = facilityCatBiz.findRootCat(repairOrder.getFacility().getFacilityCat().getId());
                tags.add(tagObject(repairOrder.getFacility().getFacilityCat().getCatName(), "blue"));
                tags.add(tagObject(rootCat.getCatName(), "blue"));
            }


            JSONObject jsonObject = new JSONObject();
            jsonObject.put("createDate", DateTimeUtils.format(repairOrder.getCreateDate(), DateTimeUtils.format(repairOrder.getCreateDate(), "yy-MM-dd HH:mm")));
            jsonObject.put("reportContent", StringConverters.null2String(repairOrder.getReportContent()));
            jsonObject.put("reportAddress", StringConverters.null2String(repairOrder.getReportAddress()));
            jsonObject.put("orderStatus", repairOrder.getRepairOrderStatusEnum().getName());
            jsonObject.put("reportCode", repairOrder.getReportCode());
            jsonObject.put("reportUser", repairOrder.getReportUser() == null ? "" : repairOrder.getReportUser().getUserName());
            jsonObject.put("receiveUserName", repairOrder.getReceiveUser() == null ? "" : repairOrder.getReceiveUser().getUserName());
            jsonObject.put("id", repairOrder.getId());
            jsonObject.put("tags", tags);
            rows.add(jsonObject);
        }
        result.put("rows", rows);
        result.put("records", records);
        return result;
    }

    private JSONObject tagObject(String name, String color) {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("name", name);
        jsonObject.put("color", color);
        return jsonObject;
    }

    //dashboard报表
    public String dashBoardInfo(String enterpriseId, String status, String date) {
        String dashHql = "select count(r.id) from RepairOrder r where r.repairOrderStatusEnum in (" + status + ")";
        if (StringUtils.isNotBlank(enterpriseId) && !enterpriseId.equals("null")) {
            dashHql = dashHql + " and r.enterprise.id=" + enterpriseId;
        }
        dashHql = dashHql + " and r.createDate like  '" + date + "%'";
        dashHql = dashHql + " and r.deleteStatus=false and r.repairOrderTypeEnum != 'AUTOMATIC_REPAIR'";

//        logger.info("拼接完成的sql语句为：{}", dashHql);
        Query query = this.entityManager.createQuery(dashHql);
        Object o = query.getSingleResult();
        return o.toString();
    }

    //dashboard报表
    public JSONArray getbetween(String enterpriseId, String start, String end, int type, boolean completed) {
        String dashHql = "select count(r.id),date_format(r.createDate,'%Y-%m-%d'),";//type 1小时统计，2天数统计
        if (type == 1) {
            dashHql = dashHql + "Hour(r.createDate)";
        } else {
            dashHql = dashHql + "Day(r.createDate)";
        }

        // 数据库中的数据格式是 2019-01-31 00:00:00  这里做一下日期时间拼接
        String sqlStart = start + " 00:00:00";
        String sqlEnd = end + " 23:59:59";

        dashHql = dashHql + "from RepairOrder r  where r.createDate>='" + sqlStart + "' and r.createDate <'" + sqlEnd + "'";
        if (StringUtils.isNotBlank(enterpriseId)) {
            dashHql = dashHql + " and r.enterprise.id=" + enterpriseId;
        }
        if (completed) {
            dashHql = dashHql + " and r.repairOrderStatusEnum = 'COMPLETED' ";
        }
        dashHql = dashHql + " and r.deleteStatus=false";
        if (type == 1) {
            dashHql = dashHql + " group by hour(r.createDate)";
        } else {
            dashHql = dashHql + " group by Day(r.createDate)";
        }
//        logger.info("拼接完成的sql语句为：{}", dashHql);
        Query query = this.entityManager.createQuery(dashHql);
        List<Object> o = query.getResultList();
        return organizationBiz.getDashBoardData(o, start, end, type);
    }

    /**
     * appdashboard 根据人员计算接单数
     *
     * @param organizationId
     * @param start
     * @param end
     * @return
     */
    public List<Object> getOrganizationReceiveNum(String organizationId, String start, String end) {
        String sql = "select COUNT(r.id),uo.system_user_id,date_format(r.receive_date,'%Y-%m-%d') as nowday,Month(r.receive_date) as nowmonth,quarter(r.receive_date)  as nowseason  from  system_user_organization uo  LEFT JOIN repair_order r " +
                " on r.receive_user_id=uo.system_user_id and r.source in ('CUSTOMER_REPORT','INSPECTION_REPAIR') and r.delete_status=false and r.receive_date >= '" + start + "' AND r.receive_date <'" + end + "' and r.receive_user_id is not null  WHERE" +
                " uo.organization_id=" + organizationId + " and uo.delete_status=false  group by uo.system_user_id";
        sql = sql + " order by uo.system_user_id";
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object> objects = query.getResultList();
        return objects;
    }

    /**
     * Charts 根据人员计算工时
     */
    public List<Object> getHoursChartsData(String startDateTime, String endDateTime, String enterpriseId, String organizationId) {
        String sql = " SELECT SUM(f.working_hours) working_hours FROM " +
                "(SELECT b.id,SUM(a.working_hours) working_hours FROM repair_order a " +
                " right JOIN SYSTEM_USER b " +
                " ON a.`receive_user_id` = b.id  AND a.repair_finish_time > '" + startDateTime + "' AND a.repair_finish_time < '" + endDateTime + "' and a.repair_order_status = 'COMPLETED' " +
                " AND a.source in ('CUSTOMER_REPORT','INSPECTION_REPAIR') " +
                " AND a.delete_status = false ";
        if (StringUtils.isNotBlank(enterpriseId)) {
            sql += " AND a.`enterprise_id` = '" + enterpriseId + "' ";
        }
        sql = sql + " LEFT JOIN system_user_organization g " +
                " ON b.id = g.system_user_id " +
                " WHERE b.delete_status=false ";

        if (StringUtils.isNotBlank(organizationId)) {
            sql += " AND g.`organization_id` = " + organizationId + " ";
        }
        sql += " " +
                " GROUP BY b.`id` " +
                " UNION ALL " +
                " SELECT  d.id,SUM(c.working_hours) working_hours FROM repair_partner c " +
                " LEFT JOIN SYSTEM_USER d " +
                " ON c.partner_id = d.id " +
                " LEFT JOIN repair_order e " +
                " ON c.repair_order_id = e.id " +
                " LEFT JOIN system_user_organization h " +
                " ON d.id = h.system_user_id " +
                " WHERE e.repair_order_status = 'COMPLETED' " +
                " AND c.delete_status = false ";
        if (StringUtils.isNotBlank(enterpriseId)) {
            sql += " AND c.`enterprise_id` = '" + enterpriseId + "' ";
        }
        if (StringUtils.isNotBlank(organizationId)) {
            sql += " AND h.`organization_id` = " + organizationId + " ";
        }
        sql += " AND c.update_date > '" + startDateTime + "' AND c.update_date < '" + endDateTime + "' " +
                " GROUP BY d.`id`) f " +
                " GROUP BY f.id " +
                " ORDER BY f.id ";
        return entityManager.createNativeQuery(sql).getResultList();
    }

    public List countScore(String score, List<Long> ids) {
        String sql = "SELECT COUNT(*) FROM repair_order " +
                "WHERE source IN ('CUSTOMER_REPORT') and delete_status = false " +
                " AND repair_order_type IN ('COMMON_ORDER','FACILITY_ORDER','400_ORDER') " +
                "AND repair_order_status = 'COMPLETED' ";
        if (StringUtils.isNotBlank(score)) {
            sql += "AND last_score in (" + score + ") ";
        }
        if (CollectionUtils.isNotEmpty(ids)) {
            sql += "AND enterprise_id in (" + StringUtils.strip(ids.toString(), "[]") + ") ";
        }
        return entityManager.createNativeQuery(sql).getResultList();
    }

    public List<Object[]> repairTop10(String enterpriseIds, String startDate, String endDate) {
        String sql = "SELECT facility_id,COUNT(*) FROM repair_order \n" +
                "WHERE delete_status = FALSE AND enterprise_id IN (" + enterpriseIds + ") AND create_date BETWEEN '" + startDate + "' AND '" + endDate + "' AND facility_id IS NOT NULL\n" +
                "GROUP BY facility_id ORDER BY count(*) DESC LIMIT 10";
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object[]> queryResultList = query.getResultList();
        return queryResultList;
    }

    public List<Object[]> areaPercent(String enterpriseIds, String startDate, String endDate) {
        String sql = "SELECT A.root_id,count(*) FROM repair_order R\n" +
                "LEFT JOIN facility F\n" +
                "ON R.facility_id = F.id\n" +
                "LEFT JOIN area A\n" +
                "ON A.id = F.area_id\n" +
                "WHERE R.delete_status = FALSE AND R.enterprise_id in (" + enterpriseIds + ") AND R.create_date BETWEEN '" + startDate + "' AND '" + endDate + "'\n" +
                "GROUP BY A.root_id";
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object[]> queryResultList = query.getResultList();
        return queryResultList;
    }

    public Long areaPercentFinishedCount(String enterpriseIds, String startDate, String endDate, String areaRootId) {
        String str1;
        if (StringUtils.isBlank(areaRootId)) {
            str1 = " AND A.root_id IS NULL ";
        } else {
            str1 = " AND A.root_id = " + areaRootId;
        }
        String sql = "SELECT count(*) FROM repair_order R\n" +
                "LEFT JOIN facility F\n" +
                "ON R.facility_id = F.id\n" +
                "LEFT JOIN area A\n" +
                "ON A.id = F.area_id\n" +
                "WHERE R.delete_status = FALSE AND R.enterprise_id in (" + enterpriseIds + ") AND R.create_date BETWEEN '" + startDate + "' AND '" + endDate + "'\n" +
                "AND R.repair_order_status != 'COMPLETED' " + str1;
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        return StringConverters.null2Long(query.getSingleResult());
    }

    public List<Object[]> repairFacilityCount(String enterpriseIds) {
        String sql = "SELECT facility_id FROM repair_order \n" +
                "WHERE delete_status = FALSE AND enterprise_id IN (" + enterpriseIds + ") \n" +
                "AND receive_date IS NOT NULL \n" +
                "AND repair_order_status NOT IN ('COMPLETED','CANCELED')\n" +
                "AND facility_id IS NOT NULL GROUP BY facility_id";
        logger.info("sql语句为：" + sql);
        Query query = this.entityManager.createNativeQuery(sql);
        List<Object[]> queryResultList = query.getResultList();
        return queryResultList;
    }

    /**
     * dashboard-故障严重程度列表
     *
     * @return
     */
    public List<Object> getFailureSeverityDashBoard(Map<String, Object> dataMap) {
        Long enterpriseId = StringConverters.ToLong(dataMap.get("enterpriseId") + "");
        String sql = "SELECT b.grade,b.color,COUNT(1) as num1 FROM repair_order a\n" +
                "LEFT JOIN repair_level b\n" +
                "ON a.report_level=b.id\n" +
                "LEFT JOIN enterprise c\n" +
                "ON a.enterprise_id=c.id\n" +
                "WHERE b.id IS NOT NULL and b.delete_status=FALSE and a.delete_status=FALSE\n";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += "AND c.parent_ids LIKE '%," + enterpriseId + ",%'\n";
        }
        sql += "GROUP BY b.grade order by num1 desc\n";

        return entityManager.createNativeQuery(sql).getResultList();
    }

    /**
     * dashboard-故障严重程度总数
     *
     * @return
     */
    public List<Object> getFailureSeverityTotalDashBoard(Map<String, Object> dataMap) {
        Long enterpriseId = StringConverters.ToLong(dataMap.get("enterpriseId") + "");
        String sql = "SELECT COUNT(1) AS num1 FROM repair_order a\n" +
                "LEFT JOIN repair_level b\n" +
                "ON a.report_level=b.id\n" +
                "LEFT JOIN enterprise c\n" +
                "ON a.enterprise_id=c.id\n" +
                "WHERE b.id IS NOT NULL and b.delete_status=FALSE and a.delete_status=FALSE\n";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += " AND c.parent_ids LIKE '%," + enterpriseId + ",%'";
        }
        return entityManager.createNativeQuery(sql).getResultList();
    }

    /**
     * dashboard-设备故障发生次数统计TOP10
     */
    public List<Object> getStatisticsEquipmentFailuresDashBoard(Map<String, Object> dataMap) {
        Long enterpriseId = StringConverters.ToLong(dataMap.get("enterpriseId") + "");
        String sql = "SELECT b.`name`,COUNT(1) as num1 FROM repair_order a\n" +
                "LEFT JOIN facility b\n" +
                "ON a.facility_id=b.id\n" +
                "LEFT JOIN enterprise c\n" +
                "ON a.enterprise_id=c.id\n" +
                "WHERE b.id IS NOT NULL and b.delete_status=FALSE and a.delete_status=FALSE\n";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += " and c.parent_ids LIKE '%," + enterpriseId + ",%' \n";
        }
        sql += "GROUP BY b.id\n" +
                "ORDER BY num1 DESC LIMIT 0,10";
        return entityManager.createNativeQuery(sql).getResultList();
    }

    public JSONArray getTags(RepairOrder repairOrder, SystemUser systemUser, String status) {
        JSONArray tags = new JSONArray();
        Date now = new Date();
        if (repairOrder.getRequireFinishTime() != null) {
            if (now.getTime() > repairOrder.getRequireFinishTime().getTime()) {
                tags.add(tagObject("逾期", "full-orange"));
            }
        }
//        else if (repairOrder.getRequirePresenceTime()!=null&&repairOrder.getPresenceTime()==null) {
//            if (now.getTime() > repairOrder.getPresenceTime().getTime()) {
//                tags.add(tagObject("逾期", "full-orange"));
//            }
//        }
        if (repairOrder.getReportLevel() != null) {
            tags.add(tagObject(repairOrder.getReportLevel().getGrade(), "orange"));
        }
        if (repairOrder.getAssign()) {
            tags.add(tagObject("被指派", "blue"));
        }
        if ("IN_AUDITING".equals(repairOrder.getRepairOrderStatusEnum().getCode())) {
            tags.add(tagObject(NumberUtil.convert(repairOrder.getAuditLevel() + 1) + "审", "blue"));
        }
        if (StringUtils.isNotBlank(repairOrder.getRepairOrderTypeEnum().getCode())) {
            if ("COMMON_ORDER".equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                tags.add(tagObject("普通", "blue"));
            } else if ("FACILITY_ORDER".equals(repairOrder.getRepairOrderTypeEnum().getCode())) {
                tags.add(tagObject("设备", "blue"));
            }
        }
        tags.add(tagObject(repairOrder.getMajorName() == null ? "" : repairOrder.getMajorName(), "blue"));
        //设备标签
        if (repairOrder.getFacility() != null && repairOrder.getFacility().getFacilityCat() != null) {
            FacilityCat rootCat = facilityCatBiz.findRootCat(repairOrder.getFacility().getFacilityCat().getId());
            tags.add(tagObject(repairOrder.getFacility().getFacilityCat().getCatName(), "blue"));
            tags.add(tagObject(rootCat.getCatName(), "blue"));
        }
        return tags;
    }

    /**
     * dashboard-获取分区响应时间
     */
    public List<Object> getRepairOrderResponseTime(Map<String, Object> dataMap) {
        Long enterpriseId = StringConverters.ToLong(dataMap.get("enterpriseId") + "");
        String sql = "SELECT a.id,a.area_name,IFNULL(b.time,0) AS time\n" +
                "FROM(SELECT a.id,a.area_name FROM area a\n" +
                "LEFT JOIN enterprise b\n" +
                "ON a.enterprise_id=b.id\n" +
                "WHERE a.delete_status=FALSE";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += " and b.parent_ids LIKE '%," + enterpriseId + ",%'";
        }
        sql += ")a\n" +
                "LEFT JOIN(SELECT b.id,b.area_name,\n" +
                "ROUND(IFNULL(SUM(TIMESTAMPDIFF(MINUTE,a.create_date,a.presence_time))/COUNT(1),0),0) AS time\n" +
                "FROM repair_order a\n" +
                "LEFT JOIN area b\n" +
                "ON a.area_id=b.id\n" +
                "LEFT JOIN enterprise c\n" +
                "ON a.enterprise_id=c.id\n" +
                "WHERE a.create_date IS NOT NULL AND a.presence_time IS NOT NULL\n" +
                "AND b.id IS NOT NULL AND a.delete_status=FALSE AND b.delete_status=FALSE\n";
        if (enterpriseId != null && enterpriseId > 0) {
            sql += "AND c.parent_ids LIKE '%," + enterpriseId + ",%'\n";
        }
        sql += "GROUP BY b.id)b\n" +
                "ON a.id=b.id\n";
        sql=sql+" order by a.id";
        return entityManager.createNativeQuery(sql).getResultList();
    }
}
